﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using CrystalDecisions.CrystalReports.Engine;

namespace SchoolManagementSystem
{
    public partial class Kelas : Form
    {
        List<int> groupID = new List<int>();
        private KelasAdd kelasadd = null;
        private int gid = 0;
        private string session = string.Empty;

        public Kelas()
        {
            InitializeComponent();
            Style.setDatagridviewStyle(dgvKelas);
        }

        private void Kelas_Load(object sender, EventArgs e)
        {
            DataTable dt = Database.getInstance().getGroups();

            // Load group
            foreach (DataRow row in dt.Rows)
            {
                groupID.Add(int.Parse(row[0].ToString()));
                cmbGrup.Items.Add(row[1].ToString());
            }

            // Load School Session
            DateTime date = DateTime.Now;
            if (date.Month < 7)
            {
                num1.Value = date.Year - 1;
                num2.Value = num1.Value + 1;
            }
        }

        private void value_changed(object sender, EventArgs e)
        {
            num2.Value = num1.Value + 1;

            if (cmbGrup.SelectedIndex < 0) return;

            this.gid = groupID[cmbGrup.SelectedIndex];
            this.session = num1.Value + " - " + num2.Value;
            updateGrid();
        }

        private void num2_valuechanged(object sender, EventArgs e)
        {
            num1.Value = num2.Value - 1;

            if (cmbGrup.SelectedIndex < 0) return;

            this.gid = groupID[cmbGrup.SelectedIndex];
            this.session = num1.Value + " - " + num2.Value;
            updateGrid();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (this.kelasadd == null || this.kelasadd.IsDisposed)
                kelasadd = new KelasAdd();

            //check whether the form has been opened or not
            if (((Main)this.MdiParent).isOpenForm(kelasadd))
            {
                kelasadd = null;
                return;
            }

            kelasadd.MdiParent = this.MdiParent;
            kelasadd.Show();
            kelasadd.Activate();
        }

        private void updateGrid()
        {
            string sql = "SELECT stu_no AS [No. Urut], s.stu_id AS [No. Induk], stu_name AS [Nama Murid], stu_gender AS [Jenis Kelamin], (stu_birthplace+', '+CONVERT(VARCHAR,stu_birthdate,105)) AS [Tempat & Tanggal Lahir] FROM Student AS s, Enrollment AS e WHERE s.stu_id=e.stu_id AND group_id=" + this.gid + " AND enr_session='" + this.session + "' ORDER BY stu_no";
            DataTable dt = Database.getInstance().query(sql);

            dgvKelas.Columns.Clear();
            dgvKelas.DataSource = dt;

            //DataGridViewButtonColumn editColumn = new DataGridViewButtonColumn();
            //editColumn.UseColumnTextForButtonValue = true;
            //editColumn.Name = "Edit";
            //editColumn.Text = "Ubah No. Urut";
            //editColumn.HeaderText = "";
            //dgvKelas.Columns.Add(editColumn);

            //DataGridViewButtonColumn deleteColumn = new DataGridViewButtonColumn();
            //deleteColumn.UseColumnTextForButtonValue = true;
            //deleteColumn.Name = "Delete";
            //deleteColumn.Text = "Keluarkan";
            //deleteColumn.HeaderText = "";
            //dgvKelas.Columns.Add(deleteColumn);
        }

        private void dgvKelas_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            DataGridViewColumn col = dgvKelas.Columns[e.ColumnIndex];
            if (col.Name == "Edit")
            {
                string number = "";
                KelasUrut kelasurut = new KelasUrut();
                DialogResult result = kelasurut.ShowDialog(this);
                if (result == DialogResult.OK)
                {
                    number = kelasurut.Number;

                    editNoUrut(dgvKelas.CurrentRow.Cells[1].Value.ToString(), number);
                }
                kelasurut.Dispose();
                updateGrid();
            }
            else if (col.Name == "Delete")
            {
                string nama = dgvKelas.CurrentRow.Cells[2].Value.ToString();

                DialogResult result = MessageBox.Show("Keluarkan murid ini dari kelas/grup?", "Konfirmasi", MessageBoxButtons.OKCancel);
                if (result == DialogResult.OK)
                {
                    delete(dgvKelas.CurrentRow.Cells[1].Value.ToString());
                    MessageBox.Show(nama + " telah dikeluarkan dari kelas/grup", "Sukses!", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }

                updateGrid();
            }
        }

        private void editNoUrut(string stu_id, string number)
        {
            string sql = "SELECT TOP 1 * FROM Enrollment WHERE stu_id = '" + stu_id + "' AND group_id = " + gid + " AND enr_session = '" + session + "'";
            DataTable dt = Database.getInstance().query(sql);
            dt.Rows[0][3] = number;
            Database.getInstance().updateDB(dt);
        }

        private void delete(string stu_id)
        {
            string sql = "DELETE FROM Enrollment WHERE stu_id = '" + stu_id + "' AND group_id = " + gid + " AND enr_session = '" + session + "'";
            Database.getInstance().query(sql);
        }

        private void dgvKelas_CellContentClick_1(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (cmbGrup.SelectedIndex < 0)
            {
                MessageBox.Show("Mohon pilih salah satu kelas/grup", "Peringatan!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }

            if (cmbGrup.SelectedIndex != -1)
            {
                // ganti cursor
                Cursor = Cursors.WaitCursor;

                string query = "SELECT CONVERT(VARCHAR, stu_no) AS stu_no_urut, e.stu_id, s.stu_name, s.stu_gender, s.stu_birthplace, CONVERT(VARCHAR(50),s.stu_birthdate,106), p.par_name, p.par_address, p.par_job FROM Enrollment AS e INNER JOIN Student AS s ON e.stu_id=s.stu_id INNER JOIN Parent AS p ON s.par_id=p.par_id WHERE e.group_id = " + this.gid + " AND e.enr_session = '" + this.session + "'";
                DataTable dTable = Database.getInstance().query(query);
                ReportDocument rptDoc = new ReportDocument();
                alwardahDataSet ds = new alwardahDataSet();

                for (int j = 0; j < dTable.Columns.Count; j++)
                {
                    string column = dTable.Columns[j].ColumnName;
                }

                for (int i = 0; i < dTable.Rows.Count; i++)
                {
                    string buffer = dTable.Rows[i].ItemArray[4].ToString();
                    buffer += ", ";
                    buffer += dTable.Rows[i].ItemArray[5].ToString();
                    dTable.Rows[i]["stu_birthplace"] = buffer;

                    string no = dTable.Rows[i].ItemArray[0].ToString();
                    dTable.Rows[i]["stu_no_urut"] = no;
                }

                ds.Tables["StudentByGroup"].Merge(dTable);

                // string appPath = (System.IO.Path.GetDirectoryName(Application.ExecutablePath) + "\\SimpleReport1.rpt");
                string appPath = (System.IO.Path.GetDirectoryName(Application.ExecutablePath));
                rptDoc.Load(appPath + "\\Report\\ReportAD4.rpt");
                rptDoc.SetDataSource(ds);

                // siapkan report viewer
                ReportForm f1 = new ReportForm(rptDoc);
                f1.ShowDialog();

                // ganti cursor
                Cursor = Cursors.Default; 
            }
        }

        private void button2_Click_1(object sender, EventArgs e)
        {
            if (cmbGrup.SelectedIndex < 0)
            {
                MessageBox.Show("Mohon pilih salah satu kelas/grup", "Peringatan!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }

            if (cmbGrup.SelectedIndex != -1)
            {
                this.gid = groupID[cmbGrup.SelectedIndex];
                string groupName = cmbGrup.Items[cmbGrup.SelectedIndex].ToString();
                int count_L = 0;
                int count_P = 0;

                // ganti cursor
                Cursor = Cursors.WaitCursor;

                string query = "SELECT s.stu_name, s.stu_gender FROM Student AS s INNER JOIN Enrollment AS e ON s.stu_id=e.stu_id WHERE e.group_id = " + this.gid + " AND e.enr_session = '" + this.session + "'";
                DataTable dTable = Database.getInstance().query(query);
                ReportDocument rptDoc = new ReportDocument();
                alwardahDataSet ds = new alwardahDataSet();

                dTable.Columns.Add("group_name");
                dTable.Columns.Add("stu_male");
                dTable.Columns.Add("stu_female");
                dTable.Columns.Add("stu_m_count");
                dTable.Columns.Add("stu_f_count");
                dTable.Columns.Add("desc");

                for (int j = 0; j < dTable.Columns.Count; j++)
                {
                    string column = dTable.Columns[j].ColumnName;
                }

                for (int i = 0; i < dTable.Rows.Count; i++)
                {
                    dTable.Rows[i]["group_name"] = groupName;
                    if (dTable.Rows[i].ItemArray[1].ToString() == "L")
                    {
                        dTable.Rows[i]["stu_male"] = "L";
                        count_L++;
                    }
                    else if (dTable.Rows[i].ItemArray[1].ToString() == "P")
                    {
                        dTable.Rows[i]["stu_female"] = "P";
                        count_P++;
                    }
                }

                if (dTable.Rows.Count == 0)
                {
                    DataRow row = dTable.NewRow();
                    row["group_name"] = "";
                    row["stu_male"] = "";
                    row["stu_female"] = "";
                    row["stu_m_count"] = "0";
                    row["stu_f_count"] = "0";
                    row["desc"] = "";
                    dTable.Rows.Add(row);
                }
                else
                {
                    dTable.Rows[0]["stu_m_count"] = "" + count_L;
                    dTable.Rows[0]["stu_f_count"] = "" + count_P;
                }

                ds.Tables["StudentByGender"].Merge(dTable);

                // string appPath = (System.IO.Path.GetDirectoryName(Application.ExecutablePath) + "\\SimpleReport1.rpt");
                string appPath = (System.IO.Path.GetDirectoryName(Application.ExecutablePath));
                rptDoc.Load(appPath + "\\Report\\ReportAD7.rpt");
                rptDoc.SetDataSource(ds);

                // siapkan report viewer
                ReportForm f1 = new ReportForm(rptDoc);
                f1.ShowDialog();

                // ganti cursor
                Cursor = Cursors.Default;
            }

          

        }

        private void cmbGrup_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmbGrup.SelectedIndex < 0) return;

            this.gid = groupID[cmbGrup.SelectedIndex];
            this.session = num1.Value + " - " + num2.Value;
            updateGrid();
        }

        private void btnUbah_Click(object sender, EventArgs e)
        {
            if (dgvKelas.SelectedRows.Count < 1)
            {
                MessageBox.Show("Mohon pilih salah satu murid", "Perhatian!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }

            string number = "";
            KelasUrut kelasurut = new KelasUrut();
            DialogResult result = kelasurut.ShowDialog(this);
            if (result == DialogResult.OK)
            {
                number = kelasurut.Number;

                editNoUrut(dgvKelas.CurrentRow.Cells[1].Value.ToString(), number);
            }
            kelasurut.Dispose();
            updateGrid();
        }

        private void btnKeluar_Click(object sender, EventArgs e)
        {
            if (dgvKelas.SelectedRows.Count < 1)
            {
                MessageBox.Show("Mohon pilih salah satu murid", "Perhatian!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }

            string nama = dgvKelas.CurrentRow.Cells[2].Value.ToString();

            DialogResult result = MessageBox.Show("Keluarkan murid ini dari kelas/grup?", "Konfirmasi", MessageBoxButtons.OKCancel);
            if (result == DialogResult.OK)
            {
                delete(dgvKelas.CurrentRow.Cells[1].Value.ToString());
                MessageBox.Show(nama + " telah dikeluarkan dari kelas/grup", "Sukses!", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

            updateGrid();
        }
    }
}

